package com.hmjk.health.background;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper{
	private static final String TAG = DatabaseHelper.class.getSimpleName();
	
	/* for mine table */
	public static final String MINE_UID = "_uid";
	public static final String MINE_ACCOUNT = "_account";
	public static final String MINE_MOBILE = "_mobile";
	public static final String MINE_NICK = "nick";
	public static final String MINE_IDCARDNO = "idcard";
	public static final String MINE_GENDER = "gender";
	public static final String MINE_ADDRESS = "address";
	public static final String MINE_EDU = "educate";
	public static final String MINE_CITY = "city";
	public static final String MINE_TIME = "uptime";
	
	/* for falist table */
	public static final String FALIST_FID = "_fid";
	public static final String FALIST_NAME = "name";
	public static final String FALIST_STATUS = "status";
	public static final String FALIST_BUILD = "build";
	public static final String FALIST_ADMIN = "admin";
	public static final String FALIST_TIME = "uptime";
	
	/* for devlist table */
	public static final String DEVLIST_TID = "_tid";
	public static final String DEVLIST_FID = "_fid";
	public static final String DEVLIST_NAME = "name";
	public static final String DEVLIST_TYPE = "type";
	public static final String DEVLIST_ADMIN = "admin";
	public static final String DEVLIST_STATUS = "status";
	public static final String DEVLIST_TIME = "uptime";
	
	/* for devsublist table */
	public static final String DEVSUBLIST_TID = "_tid";
	public static final String DEVSUBLIST_TABLE = "tablename";		//TEXT
	public static final String DEVSUBLIST_TIME = "uptime";
	
	/* for devsubinfo table */
	public static final String DEVSUBINFO_TID = "_tid";
	public static final String DEVSUBINFO_CODE = "_subcode"; //TEXT
	public static final String DEVSUBINFO_NICK = "nickname"; //TEXT
	public static final String DEVSUBINFO_BIRTHDAY = "birthday";	//TEXT
	public static final String DEVSUBINFO_GENDER = "gender";		//char(1)
	public static final String DEVSUBINFO_HEIGHT = "height";		//double
	public static final String DEVSUBINFO_WEIGHT = "weight";		//double
	public static final String DEVSUBINFO_TIME = "uptime";
	
	/*for famemberlist table */
	public static final String FAMEMBERS_FID = "_fid";
	public static final String FAMEMBERS_UID = "_uid";
	public static final String FAMEMBERS_NAME = "name";
	public static final String FAMEMBERS_STATUS = "status";
	public static final String FAMEMBERS_BUILD = "build";
	public static final String FAMEMBERS_ADMIN = "admin";
	public static final String FAMEMBERS_TIME = "uptime";
	
	/*for devset table*/
	public static final String DEVSET_TID = "_tid"; //_tid char(32)
	public static final String DEVSET_MODEL = "model"; //model TEXT
	public static final String DEVSET_DID = "did"; //did TEXT
	public static final String DEVSET_SN = "sn"; //sn TEXT
	public static final String DEVSET_MANUFAC = "manufac";//manufac TEXT
	public static final String DEVSET_SIMBIND = "simbind"; //simbind char(1)
	public static final String DEVSET_MOBILE = "mobile";//mobile varchar(11)
	public static final String DEVSET_ISSUE = "issue"; //issue TEXT
	public static final String DEVSET_ACTIVE = "active"; //active char(1)
	//public static final String DEVSET_FENCE = "fence"; //fence char(1)
	//public static final String DEVSET_FRANGE = "frange"; //frange double
	//public static final String DEVSET_FLAT = "flat"; //flat double
	//public static final String DEVSET_FLNG = "flng"; //flng double
	//public static final String DEVSET_RATE = "rate"; //rate int
	public static final String DEVSET_TIME = "uptime";
	
	/*devlocindex table*/
	public static final String DEVLOCINDEX_TID = "_tid"; //_tid char(32)
	public static final String DEVLOCINDEX_NAME = "tablename"; //tablename TEXT
	
	/*loc_$tid table*/
	public static final String LOC_LAT = "lat"; //lat double
	public static final String LOC_LNG = "lng"; //lng double
	public static final String LOC_TIME = "time"; //time BIGINT(16)
	public static final String LOC_ADDR = "addr"; //addr TEXT
	public static final String LOC_FLAG = "flag"; //TINYINT UNSIGNED
	
	/*measurereminder table*/
	public static final String MEASURE_REMINDER_TID = "_tid";
	public static final String MEASURE_REMINDER_TABLE_NAME = "tablename"; //tablename TEXT
	
	/*drugreminder table*/
	public static final String DRUG_REMINDER_TID = "_tid";
	public static final String DRUG_REMINDER_TABLE_NAME = "tablename"; //tablename TEXT	
	
	public static final String REMIND_INDEX = "reminddex";		//TINYINT UNSIGNED
	public static final String REMIND_SUB_CODE = "remind_sub_code";	//TEXT	
	public static final String REMIND_STATE = "remind_state";	
	public static final String REMIND_MODE = "remind_mode";		
	public static final String REMIND_TIME = "remindtime"; 
	public static final String REMIND_MSG = "remindmsg";
	public static final String REMIND_UPTIME = "remind_uptime";
	public static final String REMIND_TERMINAL_TYPE = "remind_terminal_type";
	public static final String REMIND_DRUG_INFO = "remind_drug_info";
	
	/*glycosy table*/
	public static final String GLYCOSY_TID = "_tid";
	public static final String GLYCOSY_SUBCODE = "subcode";
	public static final String GLYCOSY_TABLE_NAME = "tablename"; //tablename TEXT	

	public static final String GLYCOSY_VALUE = "gly_value";
	public static final String GLYCOSY_RECORD_TIME = "gly_recordtime";
	public static final String GLYCOSY_TYPE = "gly_type"; //tablename TEXT	
	
	/*bloodpressure table*/
	public static final String BP_TID = "_tid";
	public static final String BP_SUBCODE = "subcode";
	public static final String BP_TABLE_NAME = "tablename"; //tablename TEXT
	
	public static final String BP_SBP_VALUE = "sbp_value";
	public static final String BP_DBP_VALUE = "dbp_value";
	public static final String BP_PULSE_VALUE = "pulse_value";
	public static final String BP_RECORD_TIME = "bp_recordtime";
	
	private static final int DATABASE_VERSION = 2;
   	
	
	private SQLiteDatabase mDB = null;
	
	private void logd(String info){
		Log.d(TAG, info);
	}
	
	public DatabaseHelper(Context context,String name){
		this(context, name, null, DATABASE_VERSION);
	}
	
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	public void closeDB(){
		if(mDB != null)
			mDB.close();
		mDB = null;
	}
	
	public Cursor queryMineTableData(){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select * from mine", null);
			
			return c;
		}
	}
	
	public void setMineTableData(String uid, ContentValues cv){
		synchronized(this){
		if(mDB == null){
			mDB = getWritableDatabase();
		}
		
		boolean addnew = false;
		Cursor c = mDB.rawQuery("select * from mine", null);
		int count = c.getCount();
		if(count > 0){
			c.moveToFirst();
			String savedUid = c.getString(c.getColumnIndex(MINE_UID));
			if(savedUid.equals(uid)){
				String whereClause = MINE_UID + "=?";
				String[] whereArgs = new String[1];
				whereArgs[0] = uid;
				int update = mDB.update("mine", cv, whereClause, whereArgs);
				logd("setMineTableData update= "+update);
			}else{
				String sql = "DELETE FROM mine";
				mDB.execSQL(sql);
				addnew = true;
			}
		}else{
			addnew = true;
		}
		c.close();
		if(addnew){
			long add = mDB.insert("mine", null, cv);
			logd("setMineTableData add= "+add);
		}
		}
	}
	
	public void clearFalistTable(){
		synchronized(this){
		if(mDB == null){
			mDB = getWritableDatabase();
		}
		String sql = "DELETE FROM falist";
		mDB.execSQL(sql);
		}
	}
	
	public Cursor queryOneFromFalistTable(String fid){
		if(fid == null)
			return null;
		
		if(fid.trim().isEmpty())
			return null;
		
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = fid;
			String sql = "select * from falist where " + FALIST_FID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			return c;
		}
	}
	
	public Cursor queryFalistTableData(String column){
		if(column == null){
			column = "*";
		}
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select " + column + " from falist", null);
			
			return c;
		}
	}

	public void addOneItem2FalistTable(ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			long add = mDB.insert("falist", null, cv);
			logd("addOneItem2FalistTable add= "+add);
		}
	}
	
	public void deleteOneItemInFalistTable(String fid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String whereClause = FALIST_FID + "=?";
			String[] whereArgs = new String[1];
			whereArgs[0]= fid;
			
			int row = mDB.delete("falist", whereClause, whereArgs);
			logd("deleteOneItemInFalistTable delete = "+row);
		}
	}
	
	public void updateOneItemInFalistTable(String fid, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] qArgs = new String[1];
			qArgs[0] = fid;

			String sql = "select * from falist where " + FALIST_FID + "=?";
			Cursor c = mDB.rawQuery(sql, qArgs);
			int count = c.getCount();
			c.close();
			if(count > 0){
				String whereClause = FALIST_FID + "=?";
				String[] updateArgs = new String[1];
				updateArgs[0] = fid;
				
				int update = mDB.update("falist", cv, whereClause, updateArgs);
				logd("updateOneItemInFalistTable update= "+update);
			}
		}
	}
	
	public void clearDevlistTable(){
		synchronized(this){
		if(mDB == null){
			mDB = getWritableDatabase();
		}
		String sql = "DELETE FROM devlist";
		mDB.execSQL(sql);
		}
	}
	
	public Cursor queryFromDevlistTable(String tid){
		if(tid == null)
			return null;
		
		if(tid.trim().isEmpty())
			return null;
		
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = tid;
			String sql = "select * from devlist where " + DEVLIST_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			return c;
		}
	}
	
	public void updateOneInDevlistTable(String tid, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] qArgs = new String[1];
			qArgs[0] = tid;

			String sql = "select * from devlist where " + DEVLIST_TID + "=?";
			Cursor c = mDB.rawQuery(sql, qArgs);
			int count = c.getCount();
			c.close();
			if(count > 0){
				String whereClause = DEVLIST_TID + "=?";
				String[] updateArgs = new String[1];
				updateArgs[0] = tid;
				
				int update = mDB.update("devlist", cv, whereClause, updateArgs);
				logd("updateOneInDevlistTable update= "+update);
			}else{
				long add = mDB.insert("devlist", null, cv);
				logd("updateOneInDevlistTable add= "+add);
			}
		}
	}
	
	public void deleteOneInDevlistTable(String tid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String whereClause = DEVLIST_TID + "=?";
			String[] whereArgs = new String[1];
			whereArgs[0]= tid;
			
			int row = mDB.delete("devlist", whereClause, whereArgs);
			logd("deleteOneInDevlistTable delete = "+row);
		}
	}
	
	public Cursor queryDevlistTableData(){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select * from devlist", null);
			
			return c;
		}
	}
	
	public void addOneItem2DevlistTable(ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			long add = mDB.insert("devlist", null, cv);
			logd("addOneItem2DevlistTable add= "+add);
		}
	}
	
	/* for devsublist table begin */
	public Cursor queryDevsublistTableData(){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select * from devsublist", null);
			return c;
		}		
	}
	
	public Cursor queryDevsublistTableData(String tid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = tid;
			String sql = "select * from devsublist where " + DEVSUBLIST_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			return c;
		}		
	}
	
	public void deleteOneItemInDevsublistTable(String tid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String whereClause = DEVSUBLIST_TID + "=?";
			String[] whereArgs = new String[1];
			whereArgs[0]= tid;
			
			int row = mDB.delete("devsublist", whereClause, whereArgs);
			logd("deleteOneItemInDevsublistTable delete = "+row);
		}
	}
	
	public void updateOneItemInDevsublistTable(String tid,ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = tid;
			String sql = "select * from devsublist where " + DEVSUBLIST_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				//update
				String whereClause = DEVSUBLIST_TID + "=?";
				int update = mDB.update("devsublist", cv, whereClause, whereArgs);
				logd("updateOneItemInDevsublistTable update= " + update);
			}else{
				//insert
				long add = mDB.insert("devsublist", null, cv);
				logd("updateOneItemInDevsublistTable add= "+add);
			}
			if(c != null)
				c.close();
		}		
	}
	
	public String createDevSubInfoTable(String tid, ContentValues cv){
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "devsubinfo_" + tid;
			
			String sql = "create table if not exists " + name + "(_tid char(32), _subcode TEXT, nickname TEXT, birthday TEXT, gender char(1), height double, weight double, uptime BIGINT(16));";
			mDB.execSQL(sql);
			cv.put(DEVSUBLIST_TABLE, name);
			
			String[] whereArgs = new String[1];
			whereArgs[0] = tid;
			sql = "select * from devsublist where " + DEVSUBLIST_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				//update
				String whereClause = DEVSUBLIST_TID + "=?";
				int update = mDB.update("devsublist", cv, whereClause, whereArgs);
				logd("createDevSubInfoTable update= " + update);
			}else{
				//insert
				long add = mDB.insert("devsublist", null, cv);
				logd("createDevSubInfoTable add= "+add);
			}
			if(c != null)
				c.close();
			
			return name;
		}
	}
	/* for devsublist table end */
	
	/* for devsubinfo table begin */
	public void addOneInDevSubInfoTable(String table, String tid, String subcode, long time){
		//add new or update one item with new uptime
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[2];
			whereArgs[0] = tid;
			whereArgs[1] = subcode;
			String sql = "select * from " + table + " where " + DEVSUBINFO_TID + "=? and " + DEVSUBINFO_CODE + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToFirst();
				logd("addOneInDevSubInfoTable alreay has " + subcode);
				ContentValues cv = new ContentValues();
				cv.put(DEVSUBINFO_TID, tid);
				cv.put(DEVSUBINFO_CODE, subcode);
				cv.put(DEVSUBINFO_TIME, time);
				String nick = c.getString(c.getColumnIndex(DEVSUBINFO_NICK));
				String birth = c.getString(c.getColumnIndex(DEVSUBINFO_BIRTHDAY));
				String gender = c.getString(c.getColumnIndex(DEVSUBINFO_GENDER));
				double h = c.getDouble(c.getColumnIndex(DEVSUBINFO_HEIGHT));
				double w = c.getDouble(c.getColumnIndex(DEVSUBINFO_WEIGHT));
				cv.put(DEVSUBINFO_NICK, nick);
				cv.put(DEVSUBINFO_BIRTHDAY, birth);
				cv.put(DEVSUBINFO_GENDER, gender);
				cv.put(DEVSUBINFO_HEIGHT, h);
				cv.put(DEVSUBINFO_WEIGHT, w);
				
				String whereClause = DEVSUBINFO_TID + "=?" + " and " + DEVSUBINFO_CODE + "=?";
				int update = mDB.update(table, cv, whereClause, whereArgs);
				logd("addOneInDevSubInfoTable update= "+update);
			}else{
				ContentValues cv = new ContentValues();
				cv.put(DEVSUBINFO_TID, tid);
				cv.put(DEVSUBINFO_CODE, subcode);
				cv.put(DEVSUBINFO_NICK, "");
				cv.put(DEVSUBINFO_BIRTHDAY, "");
				cv.put(DEVSUBINFO_GENDER, "u");
				cv.put(DEVSUBINFO_HEIGHT, 0.0f);
				cv.put(DEVSUBINFO_WEIGHT, 0.0f);
				cv.put(DEVSUBINFO_TIME, time);
				
				long add = mDB.insert(table, null, cv);
				logd("addOneInDevSubInfoTable add= "+add);
			}
			c.close();
		}
	}
	
	public void deleteItemsInDevSubInfoListBeforeGivenTime(String table, long time){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
				
			String whereClause = DEVSUBINFO_TIME + "!=?";
			String[] whereArgs = new String[1];
			whereArgs[0]= String.valueOf(time);
				
			int row = mDB.delete(table, whereClause, whereArgs);
			logd("deleteItemsInDevSubInfoListBeforeGivenTime delete = "+row);
		}
	}
	
	public void updateOneItemInDevsubinfoTable(String table, String tid, String subcode, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[2];
			whereArgs[0] = tid;
			whereArgs[1] = subcode;
			String sql = "select * from " + table + " where " + DEVSUBINFO_TID + "=? and " + DEVSUBINFO_CODE + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				String whereClause = DEVSUBINFO_TID + "=?" + " and " + DEVSUBINFO_CODE + "=?";
				int update = mDB.update(table, cv, whereClause, whereArgs);
				logd("updateOneItemInDevsubinfoTable update= " + update);
			}else{
				long add = mDB.insert(table, null, cv);
				logd("updateOneItemInDevsubinfoTable add= " + add);
			}
			c.close();
		}		
	}
	/* for devsubinfo table end */
	
	public void clearFaMemberTableByFid(String fid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String whereClause = DatabaseHelper.FAMEMBERS_FID + "=?";
			String[] whereArgs = new String[1];
			whereArgs[0]= fid;
			
			int row = mDB.delete("famemberlist", whereClause, whereArgs);
			logd("clearFaMemberTableByFid delete = "+row);
		}
	}
	
	public void addOneItem2FaMemberTable(ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			long add = mDB.insert("famemberlist", null, cv);
			logd("addOneItem2FaMemberTable add= "+add);
		}
	}
	
	public void delateOneItemInFaMemberTable(String fid, String uid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String whereClause = FAMEMBERS_FID + "=?" + " and " + FAMEMBERS_UID + "=?";
			String[] whereArgs = {fid, uid};
			int row = mDB.delete("famemberlist", whereClause, whereArgs);
			logd("delateOneItemInFaMemberTable row= " + row);
			
		}
	}
	
	public void updateOneItemInFaMember(String fid, String uid, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String whereClause = FAMEMBERS_FID + "=?" + " and " + FAMEMBERS_UID + "=?";
			String[] whereArgs = {fid, uid};
			int update = mDB.update("famemberlist", cv, whereClause, whereArgs);
			logd("updateItemInOneFaMember update= " + update);
			
		}
	}
	
	public Cursor queryFamilyMemberTableData(){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select * from famemberlist", null);
			
			return c;
		}
	}
	
	public Cursor queryOneFamilyMemberData(String fid, String uid){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String[] qArgs = new String[2];
			qArgs[0] = fid;
			qArgs[1] = uid;

			String sql = "select * from famemberlist where _fid=? and _uid=?";
			Cursor c = mDB.rawQuery(sql, qArgs);
			return c;
		}
	}
	
	public void updateItemInDevSetTable(String tid, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] qArgs = new String[1];
			qArgs[0] = tid;

			String sql = "select * from devset where _tid=?";
			Cursor c = mDB.rawQuery(sql, qArgs);
			int count = c.getCount();
			c.close();
			if(count > 0){
				String whereClause = "_tid=?";
				String[] updateArgs = new String[1];
				updateArgs[0] = tid;
				
				int update = mDB.update("devset", cv, whereClause, updateArgs);
				logd("updateItemInDevSetTable update= "+update);
			}else{
				long add = mDB.insert("devset", null, cv);
				logd("updateItemInDevSetTable add= "+add);
			}
		}
	}
	
	public Cursor queryDevSetTableDatas(String[] tids){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String sql = "select * from devset where _tid=?";
			Cursor c = mDB.rawQuery(sql, tids);
			return c;
		}
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		/*mine table 用户资料数据*/
		String sql = "create table if not exists mine(_uid char(32), _mobile varchar(11) not null, _account TEXT not null, nick TEXT, idcard varchar(18), gender char(1), address TEXT, educate char(1), city TEXT, uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*falist table 用户所属家庭列表*/
		sql = "create table if not exists falist(_fid char(32), name TEXT, status TEXT, build char(1), admin char(1), uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*devlist table 用户所属设备列表*/
		sql = "create table if not exists devlist(_tid char(32), _fid char(32), name TEXT, type TEXT, status char(1), admin char(1), uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*famemberlist table 家庭成员列表*/
		sql = "create table if not exists famemberlist(_fid char(32), _uid char(32), name TEXT, status TEXT, build char(1), admin char(1), uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*devset table 设备详细设置*/
		sql = "create table if not exists devset(_tid char(32), model TEXT, did TEXT, sn TEXT, manufac TEXT, simbind char(1), mobile varchar(11), issue TEXT, active char(1), uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*devlocindex table 设备位置表索引*/
		sql = "create table if not exists devlocindex(_tid char(32), tablename TEXT);";
		db.execSQL(sql);
		
		/*measure reminder index table 设备位置表索引*/
		sql = "create table if not exists mremindindex(_tid char(32), tablename TEXT);";
		db.execSQL(sql);	
		
		/*drug reminder index table 设备位置表索引*/
		sql = "create table if not exists drugremindindex(_tid char(32), tablename TEXT);";
		db.execSQL(sql);		
		
		/*devsublist table 设备子账户*/
		sql = "create table if not exists devsublist(_tid char(32), tablename TEXT, uptime BIGINT(16));";
		db.execSQL(sql);
		
		/*glycosy indextable*/
		sql = "create table if not exists glycosyindex(_tid char(32), subcode TEXT, tablename TEXT);";
		db.execSQL(sql);
		
		/*bloodpressure indextable*/
		sql = "create table if not exists bpindex(_tid char(32), subcode TEXT, tablename TEXT);";
		db.execSQL(sql);
		
	}
	
	public String checkLocationTable(String tid){
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = tid;
			String sql = "select * from devlocindex where " + DEVLOCINDEX_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToNext();
				String gettid = c.getString(c.getColumnIndex(DEVLOCINDEX_TID));
				if(gettid.equals(tid)){
					name  = c.getString(c.getColumnIndex(DEVLOCINDEX_NAME));
				}
			}
			c.close();
		}
		return name;
	}
	
	public void removeOldFromLocationTable(String table, long count){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String limit = String.valueOf(count);
			Cursor c = mDB.query(table, new String[]{LOC_TIME}, null, null, null, null, null, limit);
			if(c.getCount() > 0){
				c.moveToLast();
				long time = c.getLong(c.getColumnIndex(DatabaseHelper.LOC_TIME));
				
				String whereClause = LOC_TIME + "<=?";
				String[] whereArgs = new String[1];
				whereArgs[0]= String.valueOf(time);
				
				int row = mDB.delete(table, whereClause, whereArgs);
				logd("removeOldFromLocationTable delete = "+row);
			}
			c.close();
		}
	}
	
	public boolean isTableExists(String table){
		boolean get = false;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String sql = "select count(*) as c from sqlite_master where type ='table' and name ='"+table+"';";
			Cursor c = mDB.rawQuery(sql, null);
			if(c.moveToNext()){
                int count = c.getInt(0);
                if(count>0){
                	get = true;
                }
			}
		}
		return get;
	}
	
	public void deleteTable(String table){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String sql = "DROP TABLE " + table;
			mDB.execSQL(sql);
		}
	}
	
	
	public long checkCountInTable(String table){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			String sql = "select count(*) from " + table;
		    SQLiteStatement statement = mDB.compileStatement(sql);
		    long count = statement.simpleQueryForLong();
		    return count;
		}
	}
	
	public void addOne2Table(String table, ContentValues cv){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			long add = mDB.insert(table, null, cv);
			logd("addOne2Table table=" + table +" add="+add);
		}
	}
	
	public Cursor getItemsInTable(String table){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			Cursor c = mDB.rawQuery("select * from " + table, null);
			
			return c;
		}
	}	

	public Cursor getNewestOneInLocationTable(String table){
		//String sql = "select * from " + table + " order by " + LOC_TIME + " desc limit 0,1;"; 
		
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			Cursor c = mDB.query(table, new String[]{"*"},null,null,null,null,LOC_TIME + " desc");
			return c;
		}
	}
	

	
	public String createLocationTable(String tid){
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "loc_" + tid;
		
			String sql = "create table if not exists " + name + "(lat double, lng double, time BIGINT(16), addr TEXT, flag TINYINT UNSIGNED);";
			mDB.execSQL(sql);
			
			ContentValues cv = new ContentValues();
			cv.put(DEVLOCINDEX_TID, tid);
			cv.put(DEVLOCINDEX_NAME, name);
			
			long add = mDB.insert("devlocindex", null, cv);
			logd("add2LocationIndex add= "+add);
			return name;
		}
	}

/*measure reminders*/
    public void updateReminderTable(String table_name,  String sub_account, ContentValues cv)
    {
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] qArgs = new String[1];
			qArgs[0] = sub_account;

			String sql = "select * from "+table_name+" where remind_sub_code=?";
			Cursor c = mDB.rawQuery(sql, qArgs);
			int count = c.getCount();
			c.close();
			if(count > 0){
				String whereClause = "remind_sub_code=?";
				String[] updateArgs = new String[1];
				updateArgs[0] = sub_account;
				
				int update = mDB.update(table_name, cv, whereClause, updateArgs);
				logd("updateReminderTable update= "+update);
			}else{
				long add = mDB.insert(table_name, null, cv);
				logd("updateReminderTable add= "+add);
			}
		}    	
    }
    
	public void delateOneItemInReminderTable(String table_name, String sub_account){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String whereClause = "remind_sub_code=?";
			String[] whereArgs = {sub_account};
			int row = mDB.delete(table_name, whereClause, whereArgs);
			logd("delateOneItemInReminderTable row= " + row);
			
		}
	}	
	public String checkMeasureReminderTable(String tid){     //get measure table name
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = "mreminder_" + tid;
			String sql = "select * from mremindindex where " + MEASURE_REMINDER_TID + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToNext();
				String gettid = c.getString(c.getColumnIndex(MEASURE_REMINDER_TID));
				if(gettid.equals(tid)){
					name  = c.getString(c.getColumnIndex(MEASURE_REMINDER_TABLE_NAME));
				}
			}
			c.close();
		}
		return name;
	}	
	
	
	public String createMeasureReminderTable(String tid){    //create measure table
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "mreminder_" + tid;
		
			String sql = "create table if not exists " + name + "(reminddex TEXT, remind_sub_code TEXT, remind_uptime BIGINT(16), remind_state TEXT, remind_mode TEXT, remindtime TEXT, remindmsg TEXT, remind_terminal_type TEXT);";
			mDB.execSQL(sql);
			
			ContentValues cv = new ContentValues();
			cv.put(MEASURE_REMINDER_TID, tid);
			cv.put(MEASURE_REMINDER_TABLE_NAME, name);
			
			long add = mDB.insert("mremindindex", null, cv);
			logd("createMeasureReminderTable add= "+add);
			return name;
		}
	}	
/*drug remind table*/
	public String checkDrugReminderTable(String tid){     //get measure table name
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = "drugreminder_" + tid;
			String sql = "select * from drugremindindex where " + DRUG_REMINDER_TABLE_NAME + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToNext();
				String gettid = c.getString(c.getColumnIndex(DRUG_REMINDER_TID));
				if(gettid.equals(tid)){
					name  = c.getString(c.getColumnIndex(DRUG_REMINDER_TABLE_NAME));
				}
			}
			c.close();
		}
		return name;
	}	
	
	public String createDrugReminderTable(String tid){    //create measure table
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "drugreminder_" + tid;
		
			String sql = "create table if not exists " + name + "(reminddex TEXT, remind_sub_code TEXT, remind_uptime BIGINT(16), remind_state TEXT, remind_mode TEXT, remindtime TEXT, remindmsg TEXT, remind_terminal_type TEXT, remind_drug_info TEXT);";
			mDB.execSQL(sql);
			
			ContentValues cv = new ContentValues();
			cv.put(DRUG_REMINDER_TID, tid);
			cv.put(DRUG_REMINDER_TABLE_NAME, name);
			
			long add = mDB.insert("drugremindindex", null, cv);
			logd("createDrugReminderTable add= "+add);
			return name;
		}
	}	
	
	/*glycosy indextable*/
	public String createGlycosyTable(String tid,String subcode){    //create measure table
		if((tid == null) || tid.trim().isEmpty() || (subcode == null) || subcode.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "glycosy_" + tid + "_" + subcode;
		
			String sql = "create table if not exists " + name + "(gly_value TEXT, gly_recordtime BIGINT(16), gly_type TEXT);";
			mDB.execSQL(sql);
			
			ContentValues cv = new ContentValues();
			cv.put(GLYCOSY_TID, tid);
			cv.put(GLYCOSY_SUBCODE, subcode);
			cv.put(GLYCOSY_TABLE_NAME, name);
			
			long add = mDB.insert("glycosyindex", null, cv);
			logd("createGlycosyTable add= "+add);
			return name;
		}
	}
	
	public String checkGlycosyTable(String tid,String subcode){     //get measure table name
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = "glycosy_" + tid + "_" + subcode;
			String sql = "select * from glycosyindex where " + GLYCOSY_TABLE_NAME + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToNext();
				String gettid = c.getString(c.getColumnIndex(GLYCOSY_TID));
				if(gettid.equals(tid)){
					name  = c.getString(c.getColumnIndex(GLYCOSY_TABLE_NAME));
				}
			}
			c.close();
		}
		return name;
	}
	
	public Cursor getNewestOneInGlycosyTable(String table){
		//String sql = "select * from " + table + " order by " + LOC_TIME + " desc limit 0,1;"; 
		
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			Cursor c = mDB.query(table, new String[]{"*"},null,null,null,null,GLYCOSY_RECORD_TIME + " desc");
			return c;
		}
	}
	
	public void removeOldFromGlyDataTable(String table, long time){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}				
				String whereClause = GLYCOSY_RECORD_TIME + "<?";
				String[] whereArgs = new String[1];
				whereArgs[0]= String.valueOf(time);
				
				int row = mDB.delete(table, whereClause, whereArgs);
				logd("removeOldFromGlyDataTable delete = "+row);
		}
	}
	
	public Cursor getPeriodItemsInGlycosyTable(String table, long start, long end){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}

			String[] whereArgs = new String[2];
			whereArgs[0] = Long.toString(start);
			whereArgs[1] = Long.toString(end);
			
			String sql = "select * from "+ table +" where " + GLYCOSY_RECORD_TIME + ">=?" +" and " + GLYCOSY_RECORD_TIME + "<=?" + " order by " + GLYCOSY_RECORD_TIME + " desc";
			Cursor c = mDB.rawQuery(sql, whereArgs);			
			
			return c;
		}
	}	
	
	/*bloodpressure indextable*/
	public String createBpTable(String tid,String subcode){    //create bp table
		if((tid == null) || tid.trim().isEmpty() || (subcode == null) || subcode.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized (this) {
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			name = "bloodpressure_" + tid + "_" + subcode;
			
			String sql = "create table if not exists " + name + "(sbp_value TEXT, dbp_value TEXT, bp_recordtime BIGINT(16), pulse_value TEXT);";
			mDB.execSQL(sql);
			
			ContentValues cv = new ContentValues();
			cv.put(BP_TID, tid);
			cv.put(BP_SUBCODE, subcode);
			cv.put(BP_TABLE_NAME, name);
			
			long add = mDB.insert("bpindex", null, cv);
			logd("createBpTable add= "+add);
			return name;
		}
	}
	
	public String checkBpTable(String tid,String subcode){     //get measure table name
		if((tid == null) || tid.trim().isEmpty())
			return null;
		
		String name = null;
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			
			String[] whereArgs = new String[1];
			whereArgs[0] = "bloodpressure_" + tid + "_" + subcode;
			String sql = "select * from bpindex where " + BP_TABLE_NAME + "=?";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			if(c.getCount() > 0){
				c.moveToNext();
				String gettid = c.getString(c.getColumnIndex(BP_TID));
				if(gettid.equals(tid)){
					name  = c.getString(c.getColumnIndex(BP_TABLE_NAME));
				}
			}
			c.close();
		}
		return name;
	}
	
	public Cursor getNewestOneInBpTable(String table){ 
		
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}
			Cursor c = mDB.query(table, new String[]{"*"},null,null,null,null,BP_RECORD_TIME + " desc");
			return c;
		}
	}
	
	public void removeOldFromBpDataTable(String table, long time){
		synchronized(this){
			if(mDB == null){
				mDB = getWritableDatabase();
			}				
			
			String whereClause = BP_RECORD_TIME + "<?";
			String[] whereArgs = new String[1];
			whereArgs[0]= String.valueOf(time);
			
			int row = mDB.delete(table, whereClause, whereArgs);
			logd("removeOldFromBpDataTable delete = "+row);
		}
	}
	
	public Cursor getPeriodItemsInBpTable(String table, long start, long end){
		synchronized (this) {
			if(mDB == null){
				mDB = getWritableDatabase();
			}

			String[] whereArgs = new String[2];
			whereArgs[0] = Long.toString(start);
			whereArgs[1] = Long.toString(end);
			
			String sql = "select * from "+ table +" where " + BP_RECORD_TIME + ">=?" +" and " + BP_RECORD_TIME + "<=?" + " order by " + BP_RECORD_TIME + " desc";
			Cursor c = mDB.rawQuery(sql, whereArgs);
			return c;
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		onCreate(db);
		
	}
}
